# coding: utf-8

# 因为重复导入，清理重复导入的工程商数据

import os

import set_env_path     # 请勿删除


from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def fix_repeat_import_users():
    """
    add by zhouhl 2017-10-31
    """

    find_repeat_import_user_sql = text("""
        select mobile,count(mobile) as mobile_count from user where deleted_at=0 group by mobile having mobile_count>=4 order by mobile_count desc;
    """)

    find_same_user_id_sql = text("""
        select id from user where mobile=:mobile order by updated_at desc;
    """)

    find_lastest_user_tag_sql = text("""
        select id,user_id from user_tags where user_id in :user_ids order by updated_at desc;
    """)

    update_outbound_sql = text("""
        update outbound_records set user_id=:lastest_user_id where user_id in :user_ids;
    """)

    # 更新用户标签记录SQL
    update_user_tag_sql = text("""
        update user_tags set user_id=:lastest_user_id where id=:lastest_user_tag_id;
    """)

    delete_user_tag_sql = text("""
        delete from user_tags where id in :user_tag_ids and id<>:lastest_user_tag_id;
    """)

    # 删除重复的用户记录SQL
    delect_user_sql = text("""
        delete from user where id in :user_ids and id<>:lastest_user_id;
    """)


    records = conn.execute(find_repeat_import_user_sql).fetchall()
    repeat_import_user_mobiles = [i.mobile for i in records]

    for mobile in repeat_import_user_mobiles:

        # 查找相同电话号码的用户
        tmp_result = conn.execute(
            find_same_user_id_sql,
            mobile=mobile,
        ).fetchall()
        lastest_user_id = tmp_result[0].id
        user_ids = tuple([i.id for i in tmp_result])

        # 查找最近更新过用户标签的记录
        tmp_result = conn.execute(
            find_lastest_user_tag_sql,
            user_ids=user_ids
        ).fetchall()
        lastest_user_tag_id = tmp_result[0].id
        user_tag_ids = tuple([i.id for i in tmp_result])

        # 更新外呼记录
        conn.execute(
            update_outbound_sql,
            lastest_user_id=lastest_user_id,
            user_ids=user_ids
        )

        # 更新用户标签记录，并把重复的用户标签记录删除
        conn.execute(
            delete_user_tag_sql,
            user_tag_ids=user_tag_ids,
            lastest_user_tag_id=lastest_user_tag_id,
        )

        conn.execute(
            update_user_tag_sql,
            lastest_user_id=lastest_user_id,
            lastest_user_tag_id=lastest_user_tag_id,
        )

        # 删除重复的用户记录
        conn.execute(
            delect_user_sql,
            user_ids=user_ids,
            lastest_user_id=lastest_user_id,
        )


if __name__ == '__main__':
    fix_repeat_import_users()
